﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_MaterialLoss : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        public panel_MaterialLoss()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();            
        }

        
        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.PartNo, t1.PartEnglishName, t1.UnitID, t2.RemainQuantity AS InStore,"
                + " t3.InOrderQuantity AS NotReceive, (t2.RemainQuantity + t3.InOrderQuantity) AS Total"
                + " FROM [222_Part] AS t1 INNER JOIN"
                + " [VF4MaterialStore] AS t2 ON t1.PartNo = t2.MaterialID RIGHT OUTER JOIN"
                + " [VF4MaterialNotReceive] AS t3 ON t2.MaterialID = t3.MaterialID"
                + " WHERE (t2.RemainQuantity + t3.InOrderQuantity) < 0";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                foreach (DataRow part_row in sourceDataSet.Tables[0].Rows)
                {
                    if (!string.IsNullOrEmpty(part_row["UnitID"].ToString().Trim()))
                    {
                        if (((int)(part_row["UnitID"].ToString().Trim()[0])) == 65402)
                        {
                            part_row.BeginEdit();
                            part_row["UnitID"] = "PC";
                            part_row.EndEdit();
                        }
                    }
                }

                gridMaterialLoss.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("PartName", typeof(string));
                newTable.Columns.Add("Unit", typeof(string));
                newTable.Columns.Add("InStore", typeof(float));
                newTable.Columns.Add("NotReceive", typeof(float));
                newTable.Columns.Add("Total", typeof(float));
                gridMaterialLoss.DataSource = newTable;
            }
            
            MaterialLoss.Columns[0].Caption = "Material ID";
            MaterialLoss.Columns[1].Caption = "Material Name";
            MaterialLoss.Columns[2].Caption = "Unit";
            MaterialLoss.Columns[3].Caption = "In Store";
            MaterialLoss.Columns[4].Caption = "Not Receive";
            MaterialLoss.Columns[5].Caption = "Total";
            MaterialLoss.BestFitColumns();
        }

        private void bottonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void bottonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "MaterialLoss.xlsx";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        MaterialLoss.OptionsPrint.PrintDetails = true;
                        MaterialLoss.OptionsPrint.ExpandAllDetails = true;
                        MaterialLoss.OptionsPrint.AutoWidth = false;
                        MaterialLoss.ExportToXlsx(saveFDlg.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }        
    }
}
